package com.li.sqlparse.factory;

import com.li.sqlparse.entity.ParseItemDto;
import com.li.sqlparse.entity.SetDeclareItemDto;
import com.li.sqlparse.entity.SqlConstant;
import com.li.sqlparse.entity.TableColumnLinkDto;
import com.li.sqlparse.service.SqlParseService;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;

/**
 * @author LiDong
 * @createTime 2021年06月04日 20:41:00
 */
public abstract class AbstractSqlParseServiceImpl implements SqlParseService {

    private static final Logger logger = LoggerFactory.getLogger(AbstractSqlParseServiceImpl.class);

    /**
     * 键 table 表名称
     */
    public static final String KEY_TABLE = "table";

    /**
     * 键 alias 表别名
     */
    public static final String KEY_ALIAS = "alias";

    /**
     * 键 column 字段
     */
    private static final String KEY_COLUMN = "column";


    /**
     * 解析 parseItems 中的字段和表之间的关系
     *
     * @param setDeclareItemDtos setDeclareItemDtos
     * @param parseItems         parseItems
     */
    @Override
    public final void dealTriggerDependencies(List<SetDeclareItemDto> setDeclareItemDtos, List<ParseItemDto> parseItems, Set<TableColumnLinkDto> links) {
        if (parseItems.isEmpty()) {
            return;
        }
        String fromTable = "";
        Iterator<ParseItemDto> iterator = parseItems.iterator();
        // 获得关系表 A
        while (iterator.hasNext()) {
            ParseItemDto item = iterator.next();
            if (SqlConstant.ROOT_ID.equals(item.getId())) {
                fromTable = item.getFrom();
                iterator.remove();
            }
        }
        for (ParseItemDto parseItem : parseItems) {
            String sqlType = parseItem.getSqlType();
            String to = parseItem.getTo();
            String from = parseItem.getFrom();
            List<String> columns = parseItem.getColumns();
            List<String> values = parseItem.getValues();
            List<String> columnValue = parseItem.getColumnValue();
            List<ParseItemDto> children = parseItem.getChildren();
            // 如果当前children不为空说明是子查询类型的sql,处理表和字段的关系
            if (CollectionUtils.isNotEmpty(children)) {
                Set<Map<String, Object>> linkedList = new LinkedHashSet<>();
                // 处理children 和 children 中的column 放到linkedList中
                dealLinkedList(children, linkedList, fromTable);
                for (String column : columns) {
                    dealColumnDependencies(to, column, linkedList, links);
                }
            }
            // colum 和 value 都存在的sql 关系处理
            if (SqlConstant.CREATE.equals(sqlType) && CollectionUtils.isNotEmpty(columnValue)) {
                for (String s : columnValue) {
                    addDependencyToLink(from, to, s, s, links);
                }
            }
            // 普通 insert 语句 含有 column 和 values
            if (SqlConstant.INSERT.equals(sqlType) && CollectionUtils.isNotEmpty(columns) && CollectionUtils.isNotEmpty(values)) {
                for (String column : columns) {
                    for (String value : values) {
                        if (value.equalsIgnoreCase(column)) {
                            addDependencyToLink(fromTable, to, value, column, links);
                        }
                        for (SetDeclareItemDto x : setDeclareItemDtos) {
                            if (value.equals(x.getTarget())) {
                                String columnName = x.getColumn();
                                List<String> table = x.getFromtable();
                                for (String s : table) {
                                    addDependencyToLink(s, fromTable, column, columnName, links);
                                }
                            }
                        }
                    }
                }
            }
            // select column Into table from 并且 children 为空
            if (SqlConstant.SELECT_INTO.equals(sqlType) && CollectionUtils.isEmpty(children)) {
                List<Map<String, String>> joinTableInfo = parseItem.getJoinTableInfo();
                // select into from （包含join 子句）
                if (CollectionUtils.isNotEmpty(joinTableInfo)) {
                    for (String column : columns) {
                        String tableAlias = "";
                        String lastColumn = "";
                        if (column.contains(".")) {
                            String[] split = column.split("\\.");
                            column = split[1];
                            tableAlias = split[0];
                        }
                        if (column.toUpperCase(Locale.ROOT).contains("AS")) {
                            lastColumn = column.toUpperCase(Locale.ROOT).split("AS")[1];
                            column = column.toUpperCase(Locale.ROOT).substring(0, column.indexOf("AS"));

                        }
                        column = column.toUpperCase(Locale.ROOT).trim();
                        for (Map<String, String> map : joinTableInfo) {
                            String alias = map.get(KEY_ALIAS);
                            String table = map.get(KEY_TABLE);
                            if (tableAlias.equalsIgnoreCase(alias)) {
                                if (StringUtils.isNotEmpty(lastColumn)) {
                                    addDependencyToLink(table, to, lastColumn, column, links);
                                } else {
                                    addDependencyToLink(table, to, column, column, links);
                                }
                            }
                        }
                    }
                } else {
                    columns.forEach(column -> addDependencyToLink(from, to, column, column, links));
                }
            }
        }
    }

    /**
     * 处理 sql 关系对象中 children 不为null的，处理字段别名 表名，字段来源信息
     *
     * @param children   parseItem 中 childrens
     * @param linkedList sql 子句中所包含的 column 和 table信息
     * @param from       parseItem 的from
     */
    private void dealLinkedList(List<ParseItemDto> children, Set<Map<String, Object>> linkedList, String from) {
        for (ParseItemDto item : children) {
            String itemFrom = item.getFrom();
            List<String> itemColumns = item.getColumns();
            List<ParseItemDto> itemChildren = item.getChildren();
            List<Map<String, String>> joinTableInfo = item.getJoinTableInfo();
            for (String itemColumn : itemColumns) {
                String tableName = "";
                String columnName = "";
                // 处理old. 和 new.的column
                if (itemColumn.contains("old.") || itemColumn.contains("new.")) {
                    String[] split = itemColumn.split("\\.");
                    String s = split[1].toUpperCase(Locale.ROOT);
                    if (s.contains("AS")) {
                        s = s.substring(s.indexOf("AS"));
                    }
                    tableName = from;
                    columnName = s;
                } else if (itemColumn.contains(".")) {
                    // 处理 test.name as column 类似的
                    if (Objects.nonNull(joinTableInfo)) {
                        String[] split = itemColumn.split("\\.");
                        String s = split[1].toUpperCase(Locale.ROOT);
                        if (s.contains("AS")) {
                            s = s.substring(s.indexOf("AS") + 2);
                        }
                        String tableAlias = split[0];
                        for (Map<String, String> map : joinTableInfo) {
                            String alias = map.get(KEY_ALIAS);
                            String table = map.get(KEY_TABLE);
                            if (alias.equals(tableAlias)) {
                                tableName = table;
                                break;
                            }
                        }
                        columnName = s;
                    }
                } else {
                    columnName = itemColumn;
                    tableName = itemFrom;
                }
                if (StringUtils.isNotEmpty(columnName)) {
                    Map<String, Object> map = new HashMap<>(2);
                    map.put(KEY_TABLE, tableName);
                    map.put(KEY_COLUMN, columnName);
                    linkedList.add(map);
                }
            }
            Map<String, Object> map = new HashMap<>(2);
            map.put(KEY_TABLE, itemFrom);
            map.put(KEY_COLUMN, itemColumns);
            linkedList.add(map);
            // 递归处理 children中的 column
            if (CollectionUtils.isNotEmpty(itemChildren)) {
                dealLinkedList(itemChildren, linkedList, from);
            }
        }
    }

    /**
     * 处理当前的 Column中的关系 比对links 和linkedList中的字段信息
     *
     * @param to         to
     * @param column     select 中的column
     * @param linkedList sql 子句中所包含的 column 和 table信息
     * @param links      关系
     */
    private void dealColumnDependencies(String to, String column, Set<Map<String, Object>> linkedList, Set<TableColumnLinkDto> links) {
        String orgColumn = column;
        String lastColumn = column;
        if (orgColumn.contains(".")) {
            orgColumn = orgColumn.split("\\.")[1];
        }
        String sqlAs = "AS";
        if (orgColumn.toUpperCase(Locale.ROOT).contains(sqlAs)) {
            orgColumn = orgColumn.toUpperCase(Locale.ROOT).split(sqlAs)[0].trim();
        }
        for (Map<String, Object> map : linkedList) {
            if (column.contains(".")) {
                column = column.substring(column.indexOf('.') + 1);
            }
            if (column.toUpperCase(Locale.ROOT).contains(sqlAs)) {
                column = column.toUpperCase(Locale.ROOT).substring(0, column.indexOf(sqlAs));
            }
            column = column.toUpperCase(Locale.ROOT).trim();
            String table = String.valueOf(map.get(KEY_TABLE));
            if (map.get(KEY_COLUMN) instanceof ArrayList<?>) {
                List<?> cos = (List<?>) map.get(KEY_COLUMN);
                for (Object co : cos) {
                    String res = String.valueOf(co);
                    res = res.toUpperCase(Locale.ROOT);
                    String[] splits = res.split("\\s+");
                    String col = splits[0].trim();
                    if (column.equalsIgnoreCase(col) && StringUtils.isNotEmpty(table) && !table.contains(SqlConstant.SELECT)) {
                        addDependencyToLink(table, to, col, lastColumn, links);
                        break;
                    }
                }
            } else {
                // insert into select 语句修改
                String clm = String.valueOf(map.get(KEY_COLUMN)).trim();
                String cos = "";
                if (clm.toUpperCase(Locale.ROOT).contains(sqlAs)) {
                    String[] split = clm.toUpperCase(Locale.ROOT).split(sqlAs);
                    clm = split[1].trim();
                    cos = split[0].trim();
                }
                if (clm.equalsIgnoreCase(orgColumn)) {
                    if (StringUtils.isNotEmpty(cos)) {
                        column = cos;
                    }
                    addDependencyToLink(table, to, column, lastColumn, links);
                }
            }
        }
    }


    /**
     * 添加关系到 link 集合中，该关系集合石最终待入库的关系集合
     *
     * @param from       关系来源表名称
     * @param to         关系流向表名称
     * @param fromColumn 关系来源字段
     * @param toColumn   关系流向字段
     * @param links      关系集合
     */
    private void addDependencyToLink(String from, String to, String fromColumn, String toColumn, Set<TableColumnLinkDto> links) {
        TableColumnLinkDto tableColumnLinkDto = new TableColumnLinkDto();
        if (from.startsWith("`") && from.endsWith("`")) {
            from = from.substring(1, from.length() - 1);
        }
        tableColumnLinkDto.setFromTable(from);
        if (to.contains(".")) {
            to = to.split("\\.")[1];
        }
        if (to.startsWith("`") && to.endsWith("`")) {
            to = to.substring(1, to.length() - 1);
        }
        tableColumnLinkDto.setToTable(to);
        if (toColumn.contains(".")) {
            toColumn = toColumn.split("\\.")[1];
        }
        final String as = "AS";
        if (toColumn.toUpperCase(Locale.ROOT).contains(as)) {
            String[] split = toColumn.toUpperCase(Locale.ROOT).split("AS");
            toColumn = split[1];
        }
        tableColumnLinkDto.setToColumn(toColumn);
        tableColumnLinkDto.setFromColumn(fromColumn);
        links.add(tableColumnLinkDto);
    }


}